package com.cho.core;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.LineNumberReader;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.eclipse.core.runtime.IProgressMonitor;

import com.cho.core.db.dao.TableCreate;
import com.cho.core.db.dao.TableDao;
import com.cho.core.util.StringUtils;
import com.cho.core.vo.FileVo;
import com.cho.core.vo.ProjectVo;
import com.cho.core.vo.TableVo;

public class RegCore {
	
	private static boolean LOG_MODE = false;
	private static FileVo fileVo = null;
	
	private Logger logger =  Logger.getLogger(this.getClass());
	
	private int fileCount = 1;
	private int tableCount = 1;
	private String fileFullPath;
	/*
	 *	1. 파일 검색
	 *		- 루트 디렉토리를 선택한 후 지정된 확장자를 찾아 파일 목록을 수집한다.
	 *
	 *	2. 파일을 분석하여 특정 정보를 추출한다.
	 *		- SQL
	 *			- DML 종류
	 *			- SQL statement
	 *			- 테이블
	 *			- join 조건 사용하는 컬럼 및 카운트
	 *			- where 조건 사용하는 컬럼 및 카운트
	 *		- 파일
	 *			- 파일 명
	 *			- 파일 경로
	 */

	
	/*
	 * 		- 파일 추출
	 * 		- sql 추출
	 * 		- sql 문장 상세 추출
	 */ 
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TMIS Auto-generated method stub
		try {
			ProjectVo projectVo = new ProjectVo("TEST1");
			TableCreate.runTableCreate();
			TableDao.deleteProjectInfo(projectVo.getProjectName());
			TableDao.deleteFileList(projectVo.getProjectName());
			TableDao.deleteTableList(projectVo.getProjectName());
			
			TableDao.insertProjectInfo(projectVo);
			RegCore c = new RegCore();
		
//			c.readFolder(projectVo.getProjectName(), new File("C:/jsp/"));
			c.readFolder(projectVo, new File("D:/project/eclipse/workspace/sqlAnalysisGUI/jsp/"));
//			c.readFolder(projectVo.getProjectName(), new File("D:/StudySpace/egov/eclipse-jee-juno-SR1-win32-x86_64/eclipse/workspace/sqlAnalysis/jsp/"));
//			c.readFolder(new File("C:/project/eGovFrameDev-2.6.0-FullVer/workspace/sqlAnalysis/jsp/"));
	//		File fileDirectory = new File("C:/project/eGovFrameDev-2.6.0-FullVer/workspace/sqlAnalysis/jsp/");
//			File fileDirectory = new File("D:/StudySpace/egov/eclipse-jee-juno-SR1-win32-x86_64/eclipse/workspace/sqlAnalysis/jsp/");
			
			ExcelCore excelCore = new ExcelCore();
			excelCore.makeExcel(new TableVo(projectVo.getProjectName()));
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public void readFolderForGUI(ProjectVo projectVo, File fileDirectory, IProgressMonitor monitor){
		File[] fileList = fileDirectory.listFiles(new CustomProgramFileExtensionFilter(projectVo.getIncludeFileExtension()));
		File currentFile = null;
		int fileTotalLine = 0;
		boolean isSqlFile = false;
		
		if(fileList != null){
			int fileListSize = fileList.length;
			logger("파일 갯수(폴더 수 포함):" + fileListSize);
			StringBuffer planText = null;
			
			for (int i = 0; i < fileListSize; i++) {
				currentFile = fileList[i];
				isSqlFile = StringUtils.isSqlFile(currentFile.getName());
				System.out.println(i + " currentFileExtention:" + isSqlFile);
				if(currentFile.isDirectory()){
					readFolderForGUI(projectVo, currentFile, monitor);
				}else{
					fileFullPath = currentFile.getAbsoluteFile().toString();
					BufferedReader br = null;
					LineNumberReader lr = null;
					logger("파일 경로:" + fileFullPath);
					monitor.subTask(fileFullPath );
					monitor.worked(1);
					try{
						lr = new LineNumberReader(new BufferedReader(new FileReader(new File(fileFullPath))));
						String textLine = null;
						planText = new StringBuffer();
						
						fileTotalLine = 0;
						while((textLine = lr.readLine()) != null){
							fileTotalLine++;
							planText.append(convertWord(lr, textLine, isSqlFile));
						}
						
						fileVo = new FileVo(projectVo.getProjectName(), String.valueOf(fileCount++), fileTotalLine,  currentFile.getName(), fileFullPath);
						
						TableDao.insertFileList(fileVo);
						extractSqlStatement(planText.toString(), monitor);
						//System.out.println(planText.toString());
						//logger(planText.toString());
					}catch(Exception e){
						logger(e);
					}
				}
			}
		}else{
			logger.info("파일이 존재하지 않습니다.");
		}
	}
	public void readFolder(ProjectVo projectVo, File fileDirectory){
		File[] fileList = fileDirectory.listFiles(new CustomProgramFileExtensionFilter(projectVo.getIncludeFileExtension()));
		File currentFile = null;
		int fileTotalLine = 0;
		
		if(fileList != null){
			logger("파일 갯수(폴더 수 포함):" + fileList.length);
			StringBuffer planText = null;
			
			for (int i = 0; i < fileList.length; i++) {
				currentFile = fileList[i];
				
				if(currentFile.isDirectory()){
					readFolder(projectVo, currentFile);
				}else{
					String fileFullPath = currentFile.getAbsoluteFile().toString();
					BufferedReader br = null;
					LineNumberReader lr = null;
					logger("파일 경로:" + fileFullPath);
					
					try{
						lr = new LineNumberReader(new BufferedReader(new FileReader(new File(fileFullPath))));
						String textLine = null;
						planText = new StringBuffer();
						
						fileTotalLine = 0;
						while((textLine = lr.readLine()) != null){
							fileTotalLine++;
							planText.append(convertWordForDoubleQuotation(lr, textLine));
						}
						
						fileVo = new FileVo(projectVo.getProjectName(), String.valueOf(fileCount++), fileTotalLine,  currentFile.getName(), fileFullPath);
						
						TableDao.insertFileList(fileVo);
						extractSqlStatement(planText.toString(), null);
						//System.out.println(planText.toString());
						//logger(planText.toString());
					}catch(Exception e){
						logger(e);
					}
				}
			}
		}else{
			logger.info("파일이 존재하지 않습니다.");
		}
	}
	
	/**
	 * @description: 프로그래밍 코드를 제거한다.
	 */
	private String convertWord(LineNumberReader lr, String word, boolean isSqlFile){
		
		if (isSqlFile) {
			word = convertWordForNoDoubleQuotation(lr, word);
		}else{
			word = convertWordForDoubleQuotation(lr, word);
		}
		
		return word;
	}	
	/**
	 * @description: 프로그래밍 코드를 제거한다.
	 */
	private String convertWordForDoubleQuotation(LineNumberReader lr, String word){
		
		word = extractDoubleQuotationRangeWord(word);
		word = textToVariableMark(word);
		word = throwDregs(word, lr);
		
		return word;
	}	
	/**
	 * @description: 프로그래밍 코드를 제거한다.
	 */
	private String convertWordForNoDoubleQuotation(LineNumberReader lr, String word){
		
		word = textToVariableMark(word);
		word = throwDregs(word, lr);
		
		return word;
	}	
	
	/**
	 * @description: text 중 더블쿼테이션("....")안에 있는 문장만 추출한다.
	 */
	private String extractDoubleQuotationRangeWord(String word){
		StringBuffer extractWord = new StringBuffer();
		Matcher m = Pattern.compile("\"([\\t|\\s|\\d|\\w|_|$|*|#|\\\\|%|,|\'|(|)|<|>|=|\\.|&@])+\"").matcher(word);
		
		while(m.find()){
			extractWord.append(m.group());
		}
		
		return extractWord.toString();
	}
	
	/**
	 * @description: sql문장 중 jsp 변수를 표시한다.
	 */
	private String textToVariableMark(String text){
		//ibatis와 proc의 경우의 수도 확인 필요
		return text.replaceAll("\"\"", "#PROGRAM_VARIABLE#");
	}
	
	/**
	 * @description: text 중 sql문장에 필요없는 찌꺼기를 제거한다.
	 */
	private String throwDregs(String text, LineNumberReader lr){
		StringBuffer temp = new StringBuffer(text.replaceAll("\"", "").replaceAll("\\n", "").replaceAll("\\r", "").trim());
//		String temp = text.replaceAll("\"", "").trim();
		if(temp.toString().isEmpty()){
			return "";
		}else{
			int fromIndex = temp.toString().toUpperCase().indexOf("FROM");
			
			if(fromIndex > 0){
				return "(" + lr.getLineNumber() + ")" + temp.insert(fromIndex, "(" + lr.getLineNumber() + ")") + "\n";
			}else{
				return "(" + lr.getLineNumber() + ")" + temp.toString() + "\n";
			}
		}
		//return text.replaceAll("\"", "").trim();
	}	
	
	private void extractSqlStatement(String word, IProgressMonitor monitor){
		extractSql(word);
		extractTable(word, monitor);
		extractColumn(word);
	}	
	
	private String extractSql(String word){
		
		return "";
	}	
	
	private void extractTable(String word, IProgressMonitor monitor){
		// /와 + 등 수식을 쓸 수도 있음 다양한 케이스 확인 필요 anti join 등
		//select[\s|\t][\w|\d|\s|\t|\n|,|_|*|(|)|'|\|]+[\s|\t](from)[\s|\t]([\w|\d]+)
		
		//select[\s|\t][\w|\d|\s|\t|\n|,|_|*|(|)|'|\||\+!@#$%^&~`|\.|=-\{\}\"\:\;\>\<\?]+[\s|\t](from)[\s\t\n]([\w\d\r\n\,\s\t]+)
		//delete[\s\t\n]+from[\s\t\n]+([\w@$]+)
		//UPDATE[\s\t\n]+([\w@$]+)
		//insert[\s\t\n]+into[\s\t\n]+([\w@$]+)
		extractInsertTable(word, monitor);
		extractUpdateTable(word, monitor);
		extractSelectAndDeleteTable(word, monitor);
		/*extractDeleteTable(word);
		extractSelectTable(word);*/
	}
	
	private void extractInsertTable(String word, IProgressMonitor monitor){
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)insert[\\s\\t\\n\\(\\)\\d]+into[\\s\\t\\n\\(\\)\\d]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)(insert[\\s\\t\\n\\\\d]+)?into[\\s\\t\\n\\\\d]+(\\(\\d+\\))?([\\w$]+)@?([\\w$_]+)?[\\s\\t\\n]?(values)?[\\s\\t\\n\\(\\)\\d]*\\(").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)(insert[\\s\\t\\n\\\\d]+)?into[\\s\\t\\n\\\\d]+(\\(\\d+\\))?([\\w$#_\\.]+)@?([\\w$#_\\.]+)?[\\s\\t\\n]?(values)?[\\s\\t\\n\\(\\)\\d]*\\(").matcher(word);
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)(insert[\\s\\t\\n\\r]+)?into[\\s\\t\\n\\r]+(\\(\\d+\\))?([\\w$#_\\.]+)@?([\\w$#_\\.]+)?[\\s\\t\\n]?(values)?[\\s\\t\\n\\(\\)\\d]*\\(").matcher(word);
		//										1			2											3		  	4			5					
		while(m.find()){
			String lineNumber = "";
			
			if(StringUtils.isNotEmpty(m.group(3))){
				lineNumber = StringUtils.removeParenthesis(m.group(3));
			}else if(StringUtils.isNotEmpty(m.group(1))){
				lineNumber = StringUtils.removeParenthesis(m.group(1));
			}
			
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(4), "C", m.group(5), lineNumber));
			monitor.subTask(fileFullPath+"\nInsert Table 추출!!	" + "테이블명:" + m.group(4) + m.group(5) + ", 추출된 소스 라인:" + lineNumber);
			logger.info("Insert Table 추출!!	" + "테이블명:" + m.group(4) + m.group(5) + ", 추출된 소스 라인:" + lineNumber);
		}
	}
	
	private void extractUpdateTable(String word, IProgressMonitor monitor){
		//Matcher m = Pattern.compile("(?i)\\((\\d+)\\)update[\\s\\t\\n]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)update[\\s\\t\\r\\n\\d]+(\\(\\d+\\))?([\\w$]+)@?([\\w$_]+)?").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)update[\\s\\t\\r\\n\\d]+(\\(\\d+\\))?([\\w$#_\\.]+)@?([\\w$#_\\.]+)?").matcher(word);
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)update[\\s\\t\\r\\n]+(\\(\\d+\\))?([\\w$#_\\.]+)@?([\\w$#_\\.]+)?[\\s\\t\\r\\n]+(\\(\\d+\\))?[\\s\\t\\r\\n]*(\\(\\d+\\))?set").matcher(word);
		//										1									2		   3			4
		while(m.find()){
			String lineNumber = "";
			
			if(StringUtils.isNotEmpty(m.group(2))){
				lineNumber = StringUtils.removeParenthesis(m.group(2));
			}else if(StringUtils.isNotEmpty(m.group(1))){
				lineNumber = StringUtils.removeParenthesis(m.group(1));
			}
			
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(3), "U", m.group(4), lineNumber));
			monitor.subTask(fileFullPath+"\nUpdate Table 추출!!	" + "테이블명:" + m.group(3) + m.group(4) + ", 추출된 소스 라인:" + lineNumber);
			logger.info("Update Table 추출!!	" + "테이블명:" + m.group(3) + m.group(4) + ", 추출된 소스 라인:" + lineNumber);
		}
	}
	
	private void extractDeleteTable(String word){
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)delete[\\s\\t\\n]+from[\\s\\t\\n]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
		
		while(m.find()){
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(2), "D", m.group(3), m.group(1)));
			logger.info("Delete Table 추출!!	" + "테이블명:" + m.group(2) + m.group(3) + ", 추출된 소스 라인:" + m.group(1));
		}
	}
	
	private void extractSelectTable(String word){
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)select[\\s\\t][\\w\\d\\s\\t\\n,_*()'\\|\\.]+[\\s\\t\\r\\n()]from[\\s\\t]([\\w\\d]+)").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)select[\\s|\\t][\\w|\\d|\\s|\\t|\\n|,|_|*|(|)|'|\\|]+[\\s|\\t]from[\\s|\\t]([\\w|\\d]+)").matcher(word);
		
		while(m.find()){
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(2), "R", null, m.group(1)));
			logger.info("Select Table 추출!!	" + "테이블명:" + m.group(2) + ", 추출된 소스 라인:" + m.group(1));
		}
	}
	
	private void extractSelectAndDeleteTable(String word, IProgressMonitor monitor){
//		Matcher m = Pattern.compile("(?i)(\\w*)?[\\s\\t\\r\\n]*(\\(\\d+\\))from[\\s\\t\\r\\n\\d]+(\\(\\d+\\))?([\\w$#_\\.]+)@?([\\w$#_\\.]+)?").matcher(word);
		Matcher m = Pattern.compile("(?i)(\\w*)?[\\s\\t\\r\\n]*(\\(\\d+\\))from[\\s\\t\\r\\n]+(\\(\\d+\\))?([\\w$#_\\.]+)@?([\\w$#_\\.]+)?").matcher(word);
		//									1							2									3			4			5			
		while(m.find()){
			
			String selectAndDeleteGubun = m.group(1);
			String R_AND_D = "R";
			String lineNumber = "";
			
			if(selectAndDeleteGubun != null && selectAndDeleteGubun.equalsIgnoreCase("DELETE")){
				R_AND_D = "D";
			}
			
			if(StringUtils.isNotEmpty(m.group(3))){
				lineNumber = StringUtils.removeParenthesis(m.group(3));
			}else if(StringUtils.isNotEmpty(m.group(2))){
				lineNumber = StringUtils.removeParenthesis(m.group(2));
			}
			
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(4), R_AND_D, m.group(5), lineNumber));
			monitor.subTask(fileFullPath+"\n"+R_AND_D.replace("R", "Select").replace("D", "Delete") + " Table 추출!!	" + "테이블명:" + m.group(4) + ", 추출된 소스 라인:" + lineNumber);
			logger.info(R_AND_D.replace("R", "Select").replace("D", "Delete") + " Table 추출!!	" + "테이블명:" + m.group(4) + ", 추출된 소스 라인:" + lineNumber);
		}
	}
	
	private String extractColumn(String word){
		
		return "";
	}	
	
	private void logger(Object log){
		if(logger.isDebugEnabled()){
			logger.debug(log);
		}
	}
}

/*
 * 
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)(insert[\\s\\t\\n\\(\\)\\d]+)?into[\\s\\t\\n\\(\\)\\d]+([\\w$]+)@?([\\w$_]+)?[\\s\\t\\n\\(\\)\\d]+(values)?[\\s\\t\\n\\(\\)\\d]*\\(").matcher(word);
		
		while(m.find()){
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(2), "C", m.group(3), m.group(1)));
			logger.info("Insert Table 추출!!	" + "테이블명:" + m.group(2) + m.group(3) + ", 추출된 소스 라인:" + m.group(1));
		}
	}
	
	private void extractUpdateTable(String word){
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)update[\\s\\t\\n]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)update[\\s\\t\\r\\n()\\d]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
		
		while(m.find()){
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(2), "U", m.group(3), m.group(1)));
			logger.info("Update Table 추출!!	" + "테이블명:" + m.group(2) + m.group(3) + ", 추출된 소스 라인:" + m.group(1));
		}
	}
	
	private void extractDeleteTable(String word){
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)delete[\\s\\t\\n]+from[\\s\\t\\n]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)delete[\\s\\t\\r\\n()\\d]+from[\\s\\t\\r\\n()\\d]+([\\w$]+)@?([\\w$_]+)?").matcher(word);
		
		while(m.find()){
			TableDao.insertTableList(new TableVo(fileVo.getProjectName(), fileVo.getFileSeq(), tableCount++, m.group(2), "D", m.group(3), m.group(1)));
			logger.info("Delete Table 추출!!	" + "테이블명:" + m.group(2) + m.group(3) + ", 추출된 소스 라인:" + m.group(1));
		}
	}
	
	private void extractSelectTable(String word){
		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)select[\\s|\\t][\\w|\\d|\\s|\\t|\\n|,|_|*|(|)|'|\\|]+[\\s|\\t]from[\\s|\\t]([\\w|\\d]+)").matcher(word);
//		Matcher m = Pattern.compile("(?i)\\((\\d+)\\)select[\\s\\t][\\w\\d\\s\\t\\n,_*()'\\|\\.]+[\\s\\t\\r\\n()]from[\\s\\t\\r\\n()\\d]+([\\w\\d]+)@?([\\w$_]+)?").matcher(word);
 * */
